package com.westar.core.dao;

import com.westar.base.annotation.Filed;
import com.westar.core.web.PaginationContext;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;


@Repository
public class BaseDao {

    private static Logger logger = LoggerFactory.getLogger((BaseDao.class.getName()));

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    protected JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    protected NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        if (this.namedParameterJdbcTemplate == null) {
            namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(getJdbcTemplate());
        }
        return namedParameterJdbcTemplate;
    }

    /**
     * 拼装插入Sql 语句
     * @param tableName
     * @param fields
     * @return
     */
    protected String createInsertSql(String tableName, String[] fields) {
        StringBuffer sql = new StringBuffer();
        sql.append("insert into ").append(tableName).append(" (");
        for (int i = 0; i < fields.length; i++) {
            sql.append(fields[i]);
            if (i != fields.length - 1) {
                sql.append(",");
            }
        }
        sql.append(") values (");
        for (int i = 0; i < fields.length; i++) {
            sql.append(":").append(fields[i]);
            if (i != fields.length - 1) {
                sql.append(",");
            }
        }
        sql.append(")");
        return sql.toString();
    }

    /**
     * 插入数据
     * @param tableName
     * @param fields
     * @param object
     * @return
     */
    public Integer add(final String tableName, final String[] fields, Object object) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        BeanPropertySqlParameterSource beanPropertySqlParameterSource = new BeanPropertySqlParameterSource(object);
        this.getNamedParameterJdbcTemplate().update(createInsertSql(tableName, fields), beanPropertySqlParameterSource, keyHolder, new String[] { "id" });
        return keyHolder.getKey().intValue();
    }

    /**
     * 插入数据
     * @param object
     * @return
     */
    public Integer add(Object object) {
        // 分析对象的属性和名称（对应表中的字段和表名）
        List<String> listFields = new ArrayList<String>();
        Class c = object.getClass();
        Field[] fields = c.getDeclaredFields();
        for (Field field : fields) {
            if (field.getAnnotation(Filed.class) != null) {
                listFields.add(field.getName());
            }
        }
        String tableName = c.getSimpleName().toLowerCase();
        String[] fieldsStrArray = listFields.toArray(new String[0]);
        return add(tableName, fieldsStrArray, object);
    }

    /**
     * 拼装删除Sql 语句
     * @param tableName
     * @param fields
     * @return
     */
    private String createDeleteSql(String tableName, String[] fields) {
        StringBuffer sql = new StringBuffer();
        sql.append("delete from ").append(tableName).append(" where 1=1 ");
        for (int i = 0; i < fields.length; i++) {
            sql.append(" and ").append(fields[i]).append("=?");
        }
        return sql.toString();
    }

    /**
     * 根据id删除数据
     * @param tableName
     * @param id
     */
    public void delById(String tableName, Integer id) {
        this.getJdbcTemplate().update(createDeleteSql(tableName, new String[] { "id" }), id);
    }

    /**
     * 删除一条数据
     * @param c
     * @param id
     */
    public void delById(Class c, Integer id) {
        String tableName = c.getSimpleName().toLowerCase();
        this.delById(tableName, id);
    }


    /**
     * 根据id批量删除数据
     * @param tableName
     * @param ids
     */
    public void delById(String tableName, Integer[] ids) {
        List<Object[]> batchArgs = new ArrayList<Object[]>();
        for (int i = 0; i < ids.length; i++) {
            Object[] cxslit = new Object[] { ids[i] };
            batchArgs.add(cxslit);
        }
        this.getJdbcTemplate().batchUpdate(createDeleteSql(tableName, new String[] { "id" }), batchArgs);
    }

    public void delById(Class c, Integer[] ids) {
        String tableName = c.getSimpleName().toLowerCase();
        this.delById(tableName, ids);
    }





    /**
     * 根据属性删除数据，匹配方式为相等
     * @param tableName
     * @param field
     * @param fieldValue
     */
    public void delByField(String tableName, String field, Object fieldValue) {
        this.getJdbcTemplate().update(createDeleteSql(tableName, new String[] { field }), fieldValue);
    }

    public void delByField(String tableName, String[] fields, Object[] fieldValues) {
        this.getJdbcTemplate().update(createDeleteSql(tableName, fields), fieldValues);
    }

    /**
     * 删除整个表
     * @param tableName 要删除的表的表名
     */
    public void delTable(String tableName) {
        this.getJdbcTemplate().update("delete from " + tableName);
    }

    /**
     * 设置ID自增初始值
     * @param tableName
     * @param id
     */
    public void checkIdEnt(String tableName, int id) {
        StringBuffer sql = new StringBuffer();
        sql.append("\n   declare");
        sql.append("\n      vnumber number;");
        sql.append("\n      nnumber number;");
        sql.append("\n  begin");
        sql.append("\n      select " + id + "-seq_" + tableName + ".nextval  into vnumber  from dual;");
        sql.append("\n     if vnumber<>0 then");
        sql.append("\n          execute immediate  'ALTER SEQUENCE seq_" + tableName + " INCREMENT  BY '||vnumber;");
        sql.append("\n          select seq_" + tableName + ".nextval into nnumber from dual;");
        sql.append("\n          execute immediate  'ALTER SEQUENCE seq_" + tableName + " INCREMENT  BY 1';");
        sql.append("\n      end if;");
        sql.append("\n  end;");
        this.getJdbcTemplate().update(sql.toString());
    }

    /**
     * 拼装更新Sql 语句
     * @param tableName
     * @param fields
     * @return
     */
    protected String createUpdateSql(String tableName, String[] fields) {
        StringBuffer sql = new StringBuffer();
        sql.append("update ").append(tableName).append(" set ");
        for (int i = 0; i < fields.length; i++) {
            sql.append(fields[i]).append("=:").append(fields[i]);
            if (i != fields.length - 1) {
                sql.append(",");
            }
        }
        sql.append(" where id=:id");
        return sql.toString();
    }

    /**
     * 根据sql更新
     * @param sql
     * @param object
     */
    protected void update(StringBuffer sql, Object object) {
        BeanPropertySqlParameterSource beanPropertySqlParameterSource = new BeanPropertySqlParameterSource(object);
        this.getNamedParameterJdbcTemplate().update(sql.toString(), beanPropertySqlParameterSource);
    }

    /**
     * 根据sql更新
     * @param sql
     * @param object
     */
    public void update(String sql, Object object) {
        BeanPropertySqlParameterSource beanPropertySqlParameterSource = new BeanPropertySqlParameterSource(object);
        this.getNamedParameterJdbcTemplate().update(sql.toString(), beanPropertySqlParameterSource);
    }

    /**
     * 根据id更新数据
     * @param tableName
     * @param fields
     * @param object
     */
    public void update(String tableName, String[] fields, Object object) {
        String sql = createUpdateSql(tableName, fields);
        BeanPropertySqlParameterSource beanPropertySqlParameterSource = new BeanPropertySqlParameterSource(object);
        this.getNamedParameterJdbcTemplate().update(sql, beanPropertySqlParameterSource);
    }

    /**
     * 根据id更新数据
     * @param object
     */
    public void update(Object object) {
        // 分析对象的属性和名称（对应表中的字段和表名）
        List<String> listFields = new ArrayList<String>();
        Class c = object.getClass();
        Field[] fields = c.getDeclaredFields();
        for (Field field : fields) {
            if (field.getAnnotation( Filed.class) != null) {
                field.setAccessible(true);
                try {
                    if (field.get(object) != null) {
                        listFields.add(field.getName());
                    }
                } catch (Exception e) {
                    logger.error("插入操作时，获取属性值失败", e);
                }
            }
        }
        String tableName = c.getSimpleName().toLowerCase();
        String[] fieldsStrArray = listFields.toArray(new String[0]);
        this.update(tableName, fieldsStrArray, object);
    }

    /**
     * 执行sql
     * @param sql
     * @param args
     */
    public int excuteSql(String sql, Object[] args) {
        return this.getJdbcTemplate().update(sql, args);
    }

    /**
     * 执行sql(不带参数的)
     * @param sql
     */
    public void excuteSqlNoargs(String sql) {
        this.getJdbcTemplate().execute(sql);
    }

    /**
     * 拼装分页Sql 查询分页数据（SQL SERVER）
     * @param sql
     * @param orderby
     * @return
     */
    private String createPaginationSql(String sql, String orderby) {
        // sql = sql.toLowerCase();
        int cutPoint = sql.indexOf("from ");
        String head = sql.substring(0, cutPoint).trim();
        String tail = sql.substring(cutPoint).trim();
        StringBuffer pagedQueryString = new StringBuffer();
        pagedQueryString.append("select *  from ( ");
        pagedQueryString.append(head).append(",row_number() over (").append("order by ").append(orderby).append(") as row_number ").append(tail);
        pagedQueryString.append(" ) t where row_number>").append( PaginationContext.getOffset()).append(" and row_number<=").append(PaginationContext.getOffset() + PaginationContext.getPageSize());
        return pagedQueryString.toString();
    }


    /**
     * 拼装分页Sql 查询总记录数
     * @param sql
     * @return
     */
    private String createCountSql(String sql) {
        int cutPoint = sql.indexOf("from");
        String tail = sql.substring(cutPoint).trim();
        String queryIntString = "select  count(*) " + tail;
        return queryIntString;
    }

    /**
     * 分页查询
     * @param sql
     * @param orderby
     * @param args
     * @param c
     * @return
     */
    @SuppressWarnings("unchecked")
    protected List pagedQuery(String sql, String orderby, Object[] args, Class c) {
        int totalCount = this.queryInt(createCountSql(sql), args);
        if (totalCount < 1) {
            return new ArrayList();
        }
        RowMapper rowMapper = BeanPropertyRowMapper.newInstance(c);
        List data = this.getJdbcTemplate().query(this.createPaginationSql(sql, orderby), args, rowMapper);
        // 将查询到的记录总数保存到LocalThread中
        PaginationContext.setTotalCount(totalCount);
        return data;
    }

    /**
     * 分页查询
     * @param sql
     * @param orderby
     * @param args
     * @param c
     * @return
     */	@SuppressWarnings("unchecked")
    protected List pagedQueryForOa(String sql, String orderby, Object[] args, Class c) {
        int totalCount = this.queryInt(createCountSql(sql), args);
        if (totalCount < 1) {
            return new ArrayList();
        }
        RowMapper rowMapper = BeanPropertyRowMapper.newInstance(c);
        List data = this.getJdbcTemplate().query(this.createPaginationSqlV2(sql, orderby), args, rowMapper);
        // 将查询到的记录总数保存到LocalThread中
        PaginationContext.setTotalCount(totalCount);
        return data;
    }

    /**
     * 拼装分页Sql 查询分页数据（SQL SERVER）
     * @param sql
     * @param orderby
     * @return
     */
    private String createPaginationSqlV2(String sql, String orderby) {
        StringBuffer pagedQueryString = new StringBuffer();
        pagedQueryString.append("select outer.*  from (");
        pagedQueryString.append("select inner.*,rownum as rowno from(");
        pagedQueryString.append(sql).append(null==orderby?"":" order by "+orderby);
        pagedQueryString.append(") inner where rownum <=").append(PaginationContext.getOffset() + PaginationContext.getPageSize());
        pagedQueryString.append(") outer where outer.rowno>").append(PaginationContext.getOffset());
        return pagedQueryString.toString();
    }

    /**
     * 分页查询
     * @param sql
     * @param orderby
     * @param args
     * @param c
     * @return
     */
    protected List pagedQuery(StringBuffer sql, String orderby, List<Object> args, Class c) {
        return this.pagedQuery(sql.toString(), orderby, args.toArray(), c);
    }

    /**
     * 分页查询-可以自定也分页条数的
     * @param sql
     * @param orderby
     * @param args
     * @param c
     * @return
     */
    @SuppressWarnings("unchecked")
    protected List pagedQuery(String sql, String orderby, Object[] args, int pageSize, Class c) {
        // 设置每页显示条数
        PaginationContext.setPageSize(pageSize);
        int totalCount = this.queryInt(createCountSql(sql), args);
        if (totalCount < 1) {
            return new ArrayList();
        }
        RowMapper rowMapper = BeanPropertyRowMapper.newInstance(c);
        List data = this.getJdbcTemplate().query(this.createPaginationSql(sql, orderby), args, rowMapper);
        // 将查询到的记录总数保存到LocalThread中
        PaginationContext.setTotalCount(totalCount);

        return data;
    }

    @SuppressWarnings("unchecked")
    protected List pagedQuery(String sql, String sqlCount, String orderby, Object[] args, Class c) {
        int totalCount = this.queryInt(sqlCount, args);
        if (totalCount < 1) {
            return new ArrayList();
        }
        RowMapper rowMapper = BeanPropertyRowMapper.newInstance(c);
        List data = this.getJdbcTemplate().query(this.createPaginationSql(sql, orderby), args, rowMapper);
        // 将查询到的记录总数保存到LocalThread中
        PaginationContext.setTotalCount(totalCount);
        return data;
    }

    /**
     * 条件查询
     * @param sql
     * @param args
     * @param c
     * @return
     */
    @SuppressWarnings("unchecked")
    protected List listQuery(String sql, Object[] args, Class c) {
        RowMapper rowMapper = BeanPropertyRowMapper.newInstance(c);
        List data = this.getJdbcTemplate().query(sql, args, rowMapper);
        return data;
    }

    /**
     * 条件查询
     * @param sql
     * @param args
     * @param c
     * @return
     */
    protected List listQuery(StringBuffer sql, List<Object> args, Class c) {
        return this.listQuery(sql.toString(), args.toArray(), c);
    }

    /**
     * 查询结果为int
     * @param sql
     * @param args
     * @return
     */
    protected int queryInt(String sql, Object[] args) {
        return this.getJdbcTemplate().queryForObject(sql, Integer.class, args);
    }

    /**
     * 查询结果为String
     * @param sql
     * @param args
     * @return
     */
    protected String queryString(String sql, Object[] args) {
        return this.getJdbcTemplate().queryForObject(sql, String.class, args);
    }

    /**
     * 查询结果为float
     * @param sql
     * @param args
     * @return
     */
    protected float queryFloat(String sql, Object[] args) {
        return this.getJdbcTemplate().queryForObject(sql, Float.class, args);
    }

    /**
     * 查询数量
     * @param c
     * @return
     */
    public int queryInt(Class c) {
        String tableName = c.getSimpleName().toLowerCase();
        String sql = "select count(id) from " + tableName;
        return queryInt(sql, new Object[] {});
    }

    /**
     * 根据id查询对象
     * @param c
     * @param id
     * @return
     */
    public Object objectQuery(Class c, Integer id) {
        String tableName = c.getSimpleName().toLowerCase();
        String sql = "select * from " + tableName + " where id=?";
        try {
            return this.objectQuery(sql, new Object[] { id }, c);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /**
     * 根据id查询对象（部分字段）
     * @param fields
     * @param c
     * @param id
     * @return
     */
    public Object objectQuery(String[] fields, Class c, Integer id) {
        String tableName = c.getSimpleName().toLowerCase();
        StringBuffer fieldsQueryStr = new StringBuffer();
        for (int i = 0; i < fields.length; i++) {
            fieldsQueryStr.append(fields[i]);
            if (i != fields.length - 1) {
                fieldsQueryStr.append(",");
            }
        }
        String sql = "select " + fieldsQueryStr + " from " + tableName + " where id=?";
        try {
            return this.objectQuery(sql, new Object[] { id }, c);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /**
     * 根据条件查询对象
     * @param sql
     * @param args
     * @param c
     * @return
     */
    @SuppressWarnings("unchecked")
    protected Object objectQuery(String sql, Object[] args, Class c) {
        try {
            RowMapper rowMapper = BeanPropertyRowMapper.newInstance(c);
            return this.getJdbcTemplate().queryForObject(sql, args, rowMapper);
        } catch (Exception e) {
            //e.printStackTrace();
            return null;
        }
    }

    /**
     * 查询一条数据
     * @param sql
     * @param orderby
     * @param args
     * @param c
     * @return
     */
    @SuppressWarnings("unchecked")
    protected Object listFirst(String sql, String orderby, Object[] args, Class c) {
        PaginationContext.setPageSize(1);
        RowMapper rowMapper = BeanPropertyRowMapper.newInstance(c);
        List list = this.getJdbcTemplate().query(this.createPaginationSql(sql, orderby), args, rowMapper);
        if (list != null && list.size() > 0) {
            return list.get(0);
        } else {
            return null;
        }
    }

    /**
     * 拼装Sql条件
     * @param value
     * @param sql
     * @param args
     * @param appendSql
     */
    protected void addSqlWhere(Object value, StringBuffer sql, List<Object> args, String appendSql) {
        this.addSqlWhere(value, sql, args, appendSql, 1);
    }

    /**
     * 拼装Sql条件
     * @param value
     * @param sql
     * @param args
     * @param appendSql
     * @param repetCount
     */
    protected void addSqlWhere(Object value, StringBuffer sql, List<Object> args, String appendSql, int repetCount) {
        if (value != null && (value instanceof String ? !StringUtils.isBlank(value.toString()) : true)) {
            sql.append(appendSql);
            for (int i = 0; i < repetCount; i++) {
                args.add(value);
            }
        }
    }

    /**
     * 拼装Sql条件  like
     * @param value
     * @param sql
     * @param args
     * @param appendSql
     */
    protected void addSqlWhereLike(Object value, StringBuffer sql, List<Object> args, String appendSql) {
        this.addSqlWhereLike(value, sql, args, appendSql, 1);
    }

    /**
     * 拼装Sql条件  like
     * @param value
     * @param sql
     * @param args
     * @param appendSql
     * @param repetCount
     */
    protected void addSqlWhereLike(Object value, StringBuffer sql, List<Object> args, String appendSql, int repetCount) {
        if (value != null && (value instanceof String ? !StringUtils.isBlank(value.toString()) : true)) {
            sql.append(appendSql);
            for (int i = 0; i < repetCount; i++) {
                args.add("%" + value + "%");
            }
        }
    }

    /**
     * 拼装Sql条件  like
     * @param value
     * @param sql
     * @param args
     * @param appendSql
     */
    protected void addSqlWhereLeftLike(Object value, StringBuffer sql, List<Object> args, String appendSql) {
        if (value != null && (value instanceof String ? !StringUtils.isBlank(value.toString()) : true)) {
            sql.append(appendSql);
            args.add("%" + value);
        }
    }

    /**
     * 拼装Sql条件  like
     * @param value
     * @param sql
     * @param args
     * @param appendSql
     */
    protected void addSqlWhereRightLike(Object value, StringBuffer sql, List<Object> args, String appendSql) {
        if (value != null && (value instanceof String ? !StringUtils.isBlank(value.toString()) : true)) {
            sql.append(appendSql);
            args.add(value + "%");
        }
    }

    /**
     * 拼装Sql条件  in 或者 not in
     * @param values
     * @param sql
     * @param args
     * @param appendSql
     */
    protected void addSqlWhereIn(Object[] values, StringBuffer sql, List<Object> args, String appendSql) {
        if (values != null && values.length > 0) {
            StringBuffer str = new StringBuffer();
            str.append("(");
            for (int i = 0; i < values.length; i++) {
                str.append("?");
                if (i != values.length - 1) {
                    str.append(",");
                }
                args.add(values[i]);
            }
            str.append(")");
            sql.append(appendSql.replace("?", str.toString()));
        }
    }

    /**
     * 打印SQL
     * @param sql
     * @param args
     */
    protected void printSql(String sql, Object[] args) {
        if (args != null) {
            for (Object o : args) {
                sql = sql.replaceFirst("[?]", "'" + String.valueOf(o) + "'");
            }
        }
        System.out.println(sql + "\n\n");
    }

    /**
     * 打印SQL
     * @param sql
     * @param args
     */
    protected void printSql(StringBuffer sql, List<Object> args) {
        this.printSql(sql.toString(), args.toArray());
    }

    /**
     * 分页查询
     * @param sql
     * @param orderby
     * @param args
     * @return
     */
    @SuppressWarnings("unchecked")
    protected List pagedQuery(String sql, String orderby, Object[] args) {
        int totalCount = this.queryInt(createCountSql(sql), args);
        if (totalCount < 1) {
            return new ArrayList();
        }
        List data = this.getJdbcTemplate().queryForList(this.createPaginationSql(sql, orderby), args);
        // 将查询到的记录总数保存到LocalThread中
        PaginationContext.setTotalCount(totalCount);
        return data;
    }

    /**
     * 条件查询
     * @param sql
     * @param args
     * @return
     */
    @SuppressWarnings("unchecked")
    protected List listQuery(String sql, Object[] args) {
        List data = this.getJdbcTemplate().queryForList(sql, args);
        return data;
    }

    /**
     * 初始化序列
     *
     * @param tableName
     * @return void
     * @author xll
     * @date 2018/12/14 13:54
     */
    public void initSeq(String tableName) {
        StringBuffer ss = new StringBuffer();
        ss.append("\r\n  declare");
        ss.append("\r\n      vnumber number;");
        ss.append("\r\n      nnumber number;");
        ss.append("\r\n  begin");
        ss.append("\r\n      select 1-SEQ_" + tableName + ".nextval  into vnumber  from dual;");
        ss.append("\r\n      if vnumber<>0 then");
        ss.append("\r\n          execute immediate  'ALTER SEQUENCE SEQ_" + tableName + " INCREMENT  BY '||vnumber;");
        ss.append("\r\n          select SEQ_" + tableName + ".nextval into nnumber from dual;");
        ss.append("\r\n          execute immediate  'ALTER SEQUENCE SEQ_" + tableName + " INCREMENT  BY 1';");
        ss.append("\r\n      end if;");
        ss.append("\r\n  end;");
        this.excuteSqlNoargs(ss.toString());
    }


}
